﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Best.SmartCms.Utility
{

    public class ExcelHelper
    {
        private SpreadsheetDocument _document;
        private Stream _file;
        private bool _isDispose;
        private WorksheetPart _worksheetPart;
        private SheetData _sheetData;
        private string[] _columns;
        private SharedStringTable _sharedStringTable;

        /// <summary>
        /// sheet名称
        /// </summary>
        public string SheetName { get; set; }

        /// <summary>
        ///  
        /// </summary>
        public ExcelHelper()
        {

        }

        /// <summary>
        /// 初始化Document 并且文件保存到流中
        /// 并初始化sheet名称
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        public ExcelHelper(string sheetName = "sheet1")
        {
            _file = new MemoryStream();
            InitDocument(_file);
            SheetName = sheetName;
        }

        /// <summary>
        /// 初始化带着excel完整路径
        /// </summary>
        /// <param name="filePath">excel完整路径</param>
        /// <param name="sheetName">sheet名称</param>
        public ExcelHelper(string filePath, string sheetName = "sheet1")
        {
            InitDocument(filePath);
            SheetName = sheetName;
        }

        /// <summary>
        ///  初始化从excel流中读取数据对象
        /// </summary>
        public ExcelHelper(Stream excel, int sheetIndex = 1, string[] columcNames = null)
        {
            if (excel == null || excel.CanRead == false)
            {
                throw new ArgumentException("excel content is null or don't read");
            }
            _columns = columcNames;
            _file = excel;
            _document = SpreadsheetDocument.Open(_file, false);
            var workbookPart = _document.WorkbookPart;
            _worksheetPart = workbookPart.WorksheetParts.ElementAtOrDefault(sheetIndex);
            if (_worksheetPart == null)
            {
                throw new ArgumentException("excel don't exist sheet");
            }
            _sheetData = _worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();
            var sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
            _sharedStringTable = sstpart.SharedStringTable;
        }

        private void InitDocument(string filePath)
        {
            filePath = filePath?.Trim();
            if (string.IsNullOrEmpty(filePath))
            {
                throw new ArgumentNullException(nameof(filePath), "is empty or null");
            }
            _document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
        }

        private void InitDocument(Stream stream)
        {
            if (stream == null)
            {
                throw new ArgumentNullException(nameof(stream), "is empty or null");
            }
            _document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
        }

        private void InitColumnWidth(Dictionary<string, int> columnsWith, DataColumnCollection cloums)
        {
            _document.AddWorkbookPart();
            _document.WorkbookPart.Workbook = new Workbook();
            _worksheetPart = _document.WorkbookPart.AddNewPart<WorksheetPart>();
            _worksheetPart.Worksheet = new Worksheet();
            var columns = new Columns();
            UInt32Value index = 1;
            foreach (DataColumn dataColumn in cloums)
            {
                columns.AppendChild(new Column()
                {
                    Min = index,
                    Max = index,
                    Width = columnsWith[dataColumn.ColumnName] < 15 ? 15 : columnsWith[dataColumn.ColumnName],
                    CustomWidth = true
                });
                index++;
            }
            _worksheetPart.Worksheet.Append(columns);
            _sheetData = _worksheetPart.Worksheet.AppendChild(new SheetData());
        }

        /// <summary>
        /// 导出数据到excel中并以文件流返回
        /// </summary>
        /// <param name="table">数据</param>
        /// <param name="columnsWith">每列的宽度</param>
        /// <returns></returns>
        public MemoryStream ExportExcelToStream(DataTable table, Dictionary<string, int> columnsWith)
        {
            if (table == null || table.Columns.Count < 1)
            {
                throw new ArgumentNullException("参数无有效数据");
            }
            InitColumnWidth(columnsWith, table.Columns);
            DataTableToFileOrStream(table);
            _file.Position = 0;
            return _file as MemoryStream;
        }

        private void DataTableToFileOrStream(DataTable table)
        {
            CreateHead(table.Columns);
            var index = 1;
            foreach (DataRow dataRow in table.Rows)
            {
                CreateRow(table.Columns, dataRow, index + 1);
                index++;
            }
            Save();
        }

        /// <summary>
        /// 导出excel文件到本地
        /// </summary>
        /// <param name="table">数据</param>
        /// <param name="columnsWith">每列宽度</param>
        public void ExportExcelToLocalFile(DataTable table, Dictionary<string, int> columnsWith)
        {
            if (table == null || table.Rows.Count < 1)
            {
                throw new ArgumentNullException("参数无有效数据");
            }
            InitColumnWidth(columnsWith, table.Columns);
            Hyperlinks hyperlinks = new Hyperlinks();
            this._worksheetPart.Worksheet.AppendChild(hyperlinks);
            DataTableToFileOrStream(table);
        }
        /// <summary>
        ///  导出带附件的excel
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="filePath"></param>
        /// <param name="fileName"></param>
        /// <param name="fileDic"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public string ExportDataTableToExcel(DataTable sourceTable, string filePath, string fileName,
            Dictionary<string, List<KeyValuePair<string, string>>> fileDic, string sheetName)
        {
            if (sourceTable?.Rows.Count <= 0) return "";
            var folderName = Guid.NewGuid().ToString();
            filePath = Path.Combine(filePath, folderName);
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }


            var columnsWith = sourceTable.Columns.Cast<DataColumn>().ToDictionary(sourceTableColumn => sourceTableColumn.ColumnName, sourceTableColumn => 13);
            SheetName = sheetName;
            var realFilePath = Path.Combine(filePath, fileName);
            InitDocument(realFilePath);

            WorkbookPart wbp = this._document.AddWorkbookPart();
            this._document.WorkbookPart.Workbook = new Workbook();
            this._worksheetPart = this._document.WorkbookPart.AddNewPart<WorksheetPart>();
            this._worksheetPart.Worksheet = new Worksheet();
            WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = CreateStylesheet();
            wbsp.Stylesheet.Save();
            Columns columns = new Columns();
            UInt32Value index = 1;

            MergeCells mergeCells = new MergeCells();

            Row headerRow = new Row();
            foreach (DataColumn dataColumn in sourceTable.Columns)
            {
                columns.AppendChild(new Column()
                {
                    Min = index,
                    Max = index,
                    Width = columnsWith[dataColumn.ColumnName] < 15 ? 15 : columnsWith[dataColumn.ColumnName],
                    CustomWidth = true
                });

                Cell cell = new Cell();
                cell.DataType = CellValues.String;
                cell.StyleIndex = 1;
                cell.CellValue = new CellValue(dataColumn.ColumnName);
                headerRow.AppendChild(cell);

                if (dataColumn.ColumnName == "附件")
                {
                    MergeCell mergeCell = new MergeCell() { Reference = new StringValue("G1:P1") };
                    mergeCells.Append(mergeCell);
                }

                index++;
            }

            this._worksheetPart.Worksheet.Append(columns);
            this._sheetData = this._worksheetPart.Worksheet.AppendChild(new SheetData());

            // 插入表头
            _sheetData.AppendChild(headerRow);
            this._worksheetPart.Worksheet.AppendChild(mergeCells);

            Dictionary<int, string> cellAddress = new Dictionary<int, string>()
            {
                [1] = "G",
                [2] = "H",
                [3] = "I",
                [4] = "J",
                [5] = "K",
                [6] = "L",
                [7] = "M",
                [8] = "N",
                [9] = "O",
                [10] = "p",
            };

            Hyperlinks hyperlinks = new Hyperlinks();
            this._worksheetPart.Worksheet.AppendChild(hyperlinks);
            int rowIndex = 1;
            foreach (DataRow row in sourceTable.Rows)
            {
                rowIndex++;
                var row1 = _sheetData.AppendChild(new Row());
                foreach (DataColumn column in sourceTable.Columns)
                {
                    if (column.ColumnName == "附件")
                    {
                        var key = row["序号"] == null ? "0" : row["序号"].ToString();
                        if (fileDic.TryGetValue(key, out var fileList))
                        {
                            var fileIndex = 0;
                            foreach (var file in fileList)
                            {
                                var imagePath = Path.Combine(filePath, key);
                                if (!Directory.Exists(imagePath))
                                {
                                    Directory.CreateDirectory(imagePath);
                                }
                                var fileExt = file.Key.Substring(file.Key.LastIndexOf(".", StringComparison.Ordinal) + 1,
                                    (file.Key.Length - file.Key.LastIndexOf(".", StringComparison.Ordinal) - 1));
                                var reName = $"image_{fileIndex + 1}.{fileExt}";
                                var imageFilePath = Path.Combine(imagePath, reName);
                                DownloadImage(file.Value, imageFilePath);
                                row1.AppendChild(new Cell()
                                {
                                    CellValue = new CellValue(reName),
                                    DataType = CellValues.String
                                });
                                var hyperlink = new Hyperlink() { Reference = cellAddress[fileIndex + 1] + rowIndex.ToString() };
                                var hyperlinkRelationship = _worksheetPart.AddHyperlinkRelationship(new Uri($@".\{key}\{reName}", UriKind.Relative), true);
                                hyperlink.Id = hyperlinkRelationship.Id;
                                hyperlinks.AppendChild<Hyperlink>(hyperlink);
                                fileIndex++;
                            }
                        }
                    }
                    else
                    {
                        row1.AppendChild(new Cell()
                        {
                            CellValue = new CellValue(row[column.ColumnName]?.ToString() ?? ""),
                            DataType = CellValues.String
                        });
                    }
                }
            }
            Save();
            return folderName;
        }
        /// <summary>
        /// 下载图片到本地
        /// </summary>
        /// <param name="url"></param>
        /// <param name="path"></param>
        private static void DownloadImage(string url, string path)
        {
            var req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = "GET";
            req.KeepAlive = true;
            req.ContentType = "image/*";
            var rsp = (HttpWebResponse)req.GetResponse();
            Stream stream = null;
            try
            {
                stream = rsp.GetResponseStream();
                var buffer = new byte[4 * 1024 * 1024];
                //Image.FromStream(stream).Save(path);
                using (var file = new FileStream(path, FileMode.Create))
                {
                    var count = stream.Read(buffer, 0, buffer.Length);
                    while (count > 0)
                    {
                        file.Write(buffer, 0, count);
                        count = stream.Read(buffer, 0, buffer.Length);
                    }
                }
            }
            finally
            {
                stream?.Close();
                rsp?.Close();
            }
        }
        /// <summary>
        /// 将excel数据转换到dataTable中
        /// </summary>
        /// <returns></returns>
        public DataTable ExportExcelToDatatable()
        {
            var table = new DataTable();
            var rows = _sheetData.Elements<Row>();
            var count = rows.Count();
            if (count < 1)
            {
                return table;
            }
            CreateDatataleColums(table, rows.First());
            CreateDatatableRow(table, rows);
            Dispose();
            return table;
        }

        /// <summary>
        /// 创建头
        /// </summary>
        /// <param name="cloums"></param>
        private void CreateHead(DataColumnCollection cloums)
        {

            var row = _sheetData.AppendChild(new Row());
            foreach (DataColumn dataColumn in cloums)
            {
                row.AppendChild(new Cell()
                {
                    CellValue = new CellValue(dataColumn.ColumnName),
                    DataType = CellValues.String
                });
            }
        }

        private void CreateRow(DataColumnCollection cloums, DataRow row, int rowIndex)
        {
            var row1 = _sheetData.AppendChild(new Row());
            foreach (DataColumn dataColumn in cloums)
            {
                row1.AppendChild(new Cell()
                {
                    CellValue = new CellValue(row[dataColumn.ColumnName]?.ToString() ?? ""),
                    DataType = CellValues.String
                });
                if (dataColumn.ColumnName == "图片" && string.IsNullOrEmpty(row[dataColumn.ColumnName]?.ToString()) == false)
                {
                    var linkChild = this._worksheetPart.Worksheet.GetFirstChild<Hyperlinks>();
                    if (linkChild != null)
                    {
                        Hyperlink hyperlink = new Hyperlink() { Reference = "J" + rowIndex.ToString() };
                        HyperlinkRelationship hyperlinkRelationship = this._worksheetPart.AddHyperlinkRelationship(new Uri($@".\{row[dataColumn.ColumnName]?.ToString()}", UriKind.Relative), true);
                        hyperlink.Id = hyperlinkRelationship.Id;
                        linkChild.AppendChild<Hyperlink>(hyperlink);
                    }
                }
            }
        }

        private void CreateDatataleColums(DataTable table, Row row)
        {
            if (_columns != null)
            {
                foreach (var column in _columns)
                {
                    table.Columns.Add(column);
                }
            }
            else
            {
                var index = 0;
                var columns = row.Elements<Cell>();
                _columns = new string[columns.Count()];
                foreach (var column in columns)
                {
                    if (index < 1)
                    {
                        index++;
                        continue;
                    }
                    var value = column.CellValue?.Text ?? Guid.NewGuid().ToString();
                    table.Columns.Add(value);
                    _columns[index - 1] = value;
                    index++;
                }
            }
        }

        private void CreateDatatableRow(DataTable table, IEnumerable<Row> rows)
        {
            var index = 0;
            var firstCount = rows.First().Elements<Cell>().Count();
            foreach (var row in rows)
            {
                if (index < 2)
                {
                    index++;
                    continue;
                }
                var columns = row.Elements<Cell>();
                var columnIndex = 0;
                var mark = firstCount == columns.Count();
                var newRow = table.NewRow();
                for (var i = 0; i < newRow.ItemArray.Length; i++)
                {
                    newRow.ItemArray[i] = "";
                }
                foreach (var column in columns)
                {
                    if (mark && columnIndex < 1)
                    {
                        columnIndex++;
                        continue;
                    }
                    var colstring = column.CellReference.Value.Replace($"{index + 1}", "");
                    var colIndex = 0;
                    foreach (var c in colstring)
                    {
                        colIndex = colIndex * 26 + (c - 'A' + 1);
                    }

                    if (colIndex - 1 == 0)
                    {

                    }
                    else if (column.DataType != null && column.DataType == CellValues.SharedString)
                    {
                        var ssid = int.Parse(column.CellValue.Text);
                        newRow[colIndex - 2] = _sharedStringTable.ChildElements[ssid].InnerText;
                    }
                    else
                    {
                        newRow[colIndex - 2] = column.CellValue?.Text;
                    }
                    columnIndex++;
                }

                if (newRow.ItemArray.Any(x => !string.IsNullOrWhiteSpace(x.ToString())))
                {
                    table.Rows.Add(newRow);
                }
                index++;
            }
            //for (var i = 0; i < rowsCopty.Count; i++)
            //{
            //    if (i < 2)
            //    {
            //        continue;
            //    }
            //    midRows.Add(rowsCopty[i]);
            //    if (rowsCopty.Count > (i + 1))
            //    {
            //        if (string.IsNullOrEmpty(rowsCopty[i + 1].Elements<Cell>().ElementAt(1).CellValue?.Text))
            //        {
            //            continue;
            //        }
            //    }
            //    foreach (var midRow in midRows)
            //    {
            //        var newRow = table.NewRow();
            //        var columns = midRow.Elements<Cell>();
            //        var index = 0;
            //        foreach (var column in columns)
            //        {
            //            if (index < 1)
            //            {
            //                index++;
            //                continue;
            //            }
            //            if (column.DataType != null && column.DataType == CellValues.SharedString)
            //            {
            //                int ssid = int.Parse(column.CellValue.Text);
            //                newRow[this._columns[index - 1]] = this._sharedStringTable.ChildElements[ssid].InnerText;
            //            }
            //            else
            //            {
            //                newRow[this._columns[index - 1]] = column.CellValue?.Text;
            //            }
            //            index++;
            //        }
            //        table.Rows.Add(newRow);
            //    }
            //    midRows = new List<Row>();
            //}
        }

        private void Save()
        {
            var sheets = _document.WorkbookPart.Workbook.AppendChild(new Sheets());
            sheets.AppendChild(new Sheet() { Id = _document.WorkbookPart.GetIdOfPart(_worksheetPart), SheetId = 1, Name = SheetName });
            _document.Close();//必须使用close 试了save方法 不顶用 
        }
        public void Dispose()
        {
            Dispose(true);
        }
        private void Dispose(bool idDisponse)
        {
            if (idDisponse)
            {
                if (_isDispose == false)
                {
                    _document?.Dispose();
                    _isDispose = true;
                }
            }
            _isDispose = true;
        }

        private static Stylesheet CreateStylesheet()
        {
            return new Stylesheet(
                new Fonts(
                     new Font(                                                            // Index 0 - The default font.
                    new FontSize() { Val = 11 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Calibri" }),
                     new Font(                                                               // Index 1 - The bold font.
                    new Bold(),
                    new FontSize() { Val = 11 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Calibri" }),
                new Font(                                                               // Index 2 - The Italic font.
                    new Italic(),
                    new FontSize() { Val = 11 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Calibri" }),
                new Font(                                                               // Index 2 - The Times Roman font. with 16 size
                    new FontSize() { Val = 16 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Times New Roman" })
            ),
            new Fills(
                new Fill(                                                           // Index 0 - The default fill.
                    new PatternFill() { PatternType = PatternValues.None }),
                new Fill(                                                           // Index 1 - The default fill of gray 125 (required)
                    new PatternFill() { PatternType = PatternValues.Gray125 }),
                new Fill(                                                           // Index 2 - 自定义颜色 fill.
                    new PatternFill(
                        new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "CCFFCC00" } }
                    )
                    { PatternType = PatternValues.Solid })
            ),
            new Borders(
                new Border(                                                         // Index 0 - The default border.
                    new LeftBorder(),
                    new RightBorder(),
                    new TopBorder(),
                    new BottomBorder(),
                    new DiagonalBorder()),
                new Border(                                                         // Index 1 - 边框样式
                    new LeftBorder(
                        new Color() { Auto = true }
                    )
                    { Style = BorderStyleValues.Thin },
                    new RightBorder(
                        new Color() { Auto = true }
                    )
                    { Style = BorderStyleValues.Thin },
                    new TopBorder(
                        new Color() { Auto = true }
                    )
                    { Style = BorderStyleValues.Thin },
                    new BottomBorder(
                        new Color() { Auto = true }
                    )
                    { Style = BorderStyleValues.Thin },
                    new DiagonalBorder())
            ),
            new CellFormats(
                new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 },                         // Index 0 - 默认样式.  
                new CellFormat() { FontId = 1, FillId = 2, BorderId = 1, ApplyFont = true, Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } }       // Index 1 - 表哥头样式，粗体+ FFCC99 颜色 居中
            ));
        }
    }
}
